from urllib.parse import urlencode
import requests
import pymysql

original_url = 'https://mapi.guazi.com/car-source/carList/pcList?versionId=0.0.0.0&sourceFrom=wap&deviceId=f6d49bae-f1ef-4425-8762-b003160d4e08&osv=IOS&minor=bmw&sourceType=&ec_buy_car_list_ab=&location_city=&district_id=&tag=-1&license_date=&auto_type=&driving_type=&gearbox=&road_haul=&air_displacement=&emission=&car_color=&guobie=&bright_spot_config=&seat=&fuel_type=&order=&priceRange=0,-1&tag_types=&diff_city=&intention_options=&initialPriceRange=&monthlyPriceRange=&transfer_num=&car_year=&carid_qigangshu=&carid_jinqixingshi=&cheliangjibie=&page=1&pageSize=20&city_filter=16&city=16&guazi_city=16&qpres=742969545970417664&platfromSource=wap'
requests_headers = {
    'Referer': 'https://www.guazi.com/buy?search=%257B%2522minor%2522%253A%2522bmw%2522%257D',
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.6 (KHTML,like Gecko) Chrome/57.0.2987.133 Safari/537.36',
    'X-Requests-With': 'XMLHttpRequest',
}
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute("drop database if exists AJAX1")
cursor.execute("CREATE DATABASE AJAX1 DEFAULT CHARACTER SET utf8mb4")
db.close()
db2 = pymysql.connect(host='localhost', user='root', password='123456', database="AJAX", port=3306)
cursor2 = db2.cursor()
cursor2.execute("DROP TABLE IF EXISTS ajax1")
sql1 = """CREATE TABLE `ajax1`(
      `car_name` char(20) NOT NULL,
      `id` int (10) NOT NULL AUTO_INCREMENT,
         PRIMARY KEY (`id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor2.execute(sql1)
print("Created table Successfull.")
def get_one(cityid):
    p = {
        'cityid': cityid
    }
    complete_url = original_url + urlencode(p)
    try:
        response = requests.get(url=complete_url, params=requests_headers)
        if response.status_code == 200:
            return response.json()
    except requests.ConnectionError as e:
        print('Error', e.args)


def parse_three(json):
    if json:
        for i in json:
            for b in i.get('SeriesList'):
                item_list = b.get('Name')
                item_list2 = b.get('Id')
                print(item_list + ':' + str(item_list2))
                sql2='INSERT INTO ajax(car_name,id) VALUES(%s,%s)'
                try:
                    cursor2.execute(sql2,(item_list,item_list2))
                    db2.commit()
                except:
                    db2.rollback()
if __name__=='__main__':
    city_list=[{'哈尔滨':'93'},{'重庆':'15'},{'成都':'45'}]
for city in city_list:
    jo=get_one(city.values())
    parse_three(jo)
